package dao.impl;

import dao.FilesDao;
import dao.UsersDao;
import dao.pojo.Files;
import dao.pojo.Users;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;

/**
 * Created by IntelliJ IDEA.
 * User: winstars
 * Date: 2007-3-1
 * Time: 22:52:07
 * To change this template use File | Settings | File Templates.
 */
public class FilesDaoImpl extends BaseDaoImpl implements FilesDao {

	public void addFile(Files file) {
		Connection conn=getPool().getConnection();
		
		PreparedStatement pstmt=null;
		String sql="INSERT INTO files(filename,file_info,file_size,user_id,submit_time)VALUES(?,?,?,?,?)";
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, file.getFileName());
			pstmt.setString(2, file.getFileInfo());
			pstmt.setString(3, file.getFileSize());
			pstmt.setInt(4, file.getUser().getId());
			pstmt.setTimestamp(5, new Timestamp(file.getSubmitTime().getTime()));
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			getPool().freeConnection(conn);
		}
		
	}

	public void deleteFilesByUser(Users user) {
		Connection conn=getPool().getConnection();
		
		PreparedStatement pstmt=null;
		String sql="DELETE FROM files WHERE user_id=?";
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, user.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			getPool().freeConnection(conn);
		}
		
	}

    public void deleteFileById(Files file) {
    	Connection conn=getPool().getConnection();

		PreparedStatement pstmt=null;
		String sql="DELETE FROM files WHERE id=?";
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, file.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			getPool().freeConnection(conn);
		}
    }

    public void saveFile(Files file) {
    	Connection conn=getPool().getConnection();
		
		PreparedStatement pstmt=null;
		String sql="UPDATE files SET filename=?,file_info=?,file_size=?,user_id=?,submit_time=? WHERE id=?";
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, file.getFileName());
			pstmt.setString(2, file.getFileInfo());
			pstmt.setString(3, file.getFileSize());
			pstmt.setInt(4, file.getUser().getId());
			pstmt.setTimestamp(5, new Timestamp(file.getSubmitTime().getTime()));
			pstmt.setInt(6, file.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			getPool().freeConnection(conn);
		}
		
	}


	private UsersDao usersDao;

    protected UsersDao getUsersDao() {
		return usersDao;
	}

	public void setUsersDao(UsersDao usersDao) {
		this.usersDao = usersDao;
	}

    public Files getFilesById(Files file) {
    	Connection conn=getPool().getConnection();
        
        Files files=null;
        String sql = "SELECT * FROM files WHERE id=?";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, file.getId());
            rs = pstmt.executeQuery();
            while(rs.next()){
            	files=new Files();
                files.setId(rs.getInt("id"));
                files.setFileName(rs.getString("filename"));
                files.setFileInfo(rs.getString("file_info"));
                Users user=new Users();
                user.setId(rs.getInt("user_id"));
                files.setUser(usersDao.getUsersById(user));
                Date date=new Date(rs.getTimestamp("submit_time").getTime());
                files.setSubmitTime(new Date(rs.getTimestamp("submit_time").getTime()));
            }
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }finally{
			getPool().freeConnection(conn);
		}
        return files;
    }
    
    
    public ArrayList<Files> getAllFiles() {
    	Connection conn=getPool().getConnection();
    	
        ArrayList<Files> filesList=new ArrayList<Files>();
        String sql = "SELECT * FROM files";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()){
            	Files files=new Files();
                files.setId(rs.getInt("id"));
                files.setFileName(rs.getString("filename"));
                files.setFileInfo(rs.getString("file_info"));
                files.setFileSize(rs.getString("file_size"));
                Users user=new Users();
                user.setId(rs.getInt("user_id"));
                files.setUser(usersDao.getUsersById(user));
                files.setSubmitTime(new Date(rs.getTimestamp("submit_time").getTime()));
                filesList.add(files);
            }
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }finally{
			getPool().freeConnection(conn);
		}
        return filesList;
	}
}
